/*
 * nlsync
 * Copyright (c) 2010 apechurin 
 * 
 * Permission is hereby granted, free of charge, to any person
 * obtaining a copy of this software and associated documentation
 * files (the "Software"), to deal in the Software without
 * restriction, including without limitation the rights to use,
 * copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the
 * Software is furnished to do so, subject to the following
 * conditions:
 *
 * The above copyright notice and this permission notice shall be
 * included in all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
 * OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
 * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
 * HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
 * WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
 * OTHER DEALINGS IN THE SOFTWARE.
 */
using System;
using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;

namespace nlsync.Xls
{
    /// <summary>
    /// Provides access to an Excel sheet data. Implements cache of cell values
    /// to improve performance.
    /// </summary>
    class XlsSheet
    {
        #region Constructor

        /// <summary>
        /// Constructs an object for the specified sheet. The specified range
        /// of cells is copied to a managed array (cache) and can be accessed
        /// via the class.
        /// </summary>
        /// <param name="sheet">The sheet.</param>
        /// <param name="rows">The initial number of rows from the row 1.</param>
        /// <param name="columns">The number of columns we need. Only these
        ///     columns can be accessed.</param>
        public XlsSheet(Excel.Worksheet sheet, int rows, int columns)
        {
            mSheet = sheet;
            mColumns = columns;

            if (rows > 0)
            {
                Excel.Range cell = sheet.get_Range("a1", Type.Missing);
                Excel.Range lastCell = cell.get_Offset(rows - 1, columns - 1);
                Excel.Range range = sheet.get_Range("a1", lastCell);

                mData = (object[,])range.get_Value(
                    Excel.XlRangeValueDataType.xlRangeValueDefault);

                int lower = mData.GetLowerBound(0);
                int upper = mData.GetUpperBound(0);

                for (int i = lower; i <= upper; i++)
                    mRowsMap.Add(i);
            }
            else
            { 
                // add some number of free rows
                mData = (object[,])Array.CreateInstance(typeof(object),
                    new int[] { 5, columns },
                    new int[] { 1, 1 }
                );

                int lower = mData.GetLowerBound(0);
                int upper = mData.GetUpperBound(0);

                for (int i = lower; i <= upper; i++)
                    mFreeRows.Add(i);
            }
        }

        #endregion

        #region Public methods

        /// <summary>
        /// Gets the number of rows on the sheet.
        /// </summary>
        public int RowsCount
        {
            get
            {
                return mRowsMap.Count;
            }
        }

        /// <summary>
        /// Gets the number of columns on the sheet.
        /// </summary>
        public int ColumnsCount
        {
            get
            {
                return mColumns;
            }
        }

        /// <summary>
        /// Gets a value of a specified cell.
        /// </summary>
        /// <param name="row">The row.</param>
        /// <param name="column">The column.</param>
        /// <returns>The value.</returns>
        public string GetValue(int row, int column)
        {
            object o = mData[mRowsMap[row - LowerRow], column];
            return o != null ? o.ToString() : "";
        }

        /// <summary>
        /// Sets a value of a specified cell.
        /// </summary>
        /// <param name="row">The row.</param>
        /// <param name="column">The column.</param>
        /// <param name="value">The value.</param>
        /// <returns>The updated cell range.</returns>
        public Excel.Range SetValue(int row, int column, string value)
        {
            mData[mRowsMap[row - LowerRow], column] = value;

            Excel.Range c = (Excel.Range)mSheet.Cells.get_Item(row, column);
            c.Value2 = value;

            return c;
        }

        /// <summary>
        /// Add a new row.
        /// </summary>
        /// <returns>The new row.</returns>
        public int AddRow()
        {
            if (mFreeRows.Count == 0)
                ExtendDataArray();

            int row = mFreeRows[mFreeRows.Count - 1];
            mFreeRows.RemoveAt(mFreeRows.Count - 1);

            mRowsMap.Add(row);

            // reset all cells of the new row
            for (int i = 1; i < ColumnsCount; i++)
                mData[row, i] = null;

            return LowerRow + (mRowsMap.Count - 1);
        }

        /// <summary>
        /// Removes the row.
        /// </summary>
        /// <param name="row">The row.</param>
        public void RemoveRow(int row)
        {
            int idx = mRowsMap[row - LowerRow];
            mRowsMap.RemoveAt(row - LowerRow);
            mFreeRows.Add(idx);

            Excel.Range cell = (Excel.Range)mSheet.Cells.get_Item(row, 1);
            Excel.Range range = cell.EntireRow.Cells;

            range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
        }

        /// <summary>
        /// Gets an Excel sheet.
        /// </summary>
        public Excel.Worksheet Worksheet
        {
            get
            {
                return mSheet;
            }
        }
        private Excel.Worksheet mSheet;

        /// <summary>
        /// Clears contents of the sheet.
        /// </summary>
        public void Clear()
        {
            mRowsMap.Clear();
            mFreeRows.Clear();

            int lower = mData.GetLowerBound(0);
            int upper = mData.GetUpperBound(0);

            // all rows are free
            for (int i = lower; i <= upper; i++)
                mFreeRows.Add(i);

            // clear the worksheet
            mSheet.Cells.Delete(Excel.XlDirection.xlUp);
        }

        #endregion

        #region Implementation

        /// <summary>
        /// Gets the lower row.
        /// </summary>
        internal int LowerRow
        {
            get
            {
                return mData.GetLowerBound(0);
            }
        }

        /// <summary>
        /// Extends mData array.
        /// </summary>
        private void ExtendDataArray()
        {
            int originalLength = mData.GetLength(0);

            object[,] copy = (object[,])Array.CreateInstance(typeof(object),
                new int[] { originalLength * 2, mData.GetLength(1) },
                new int[] { mData.GetLowerBound(0), mData.GetLowerBound(1) }
            );
            Array.Copy(mData, copy, mData.GetLength(0) * mData.GetLength(1));

            mData = copy;

            // new rows are free
            int upper = mData.GetUpperBound(0);
            for (int row = originalLength + 1; row <= upper; row++)
                mFreeRows.Add(row);
        }

        /// <summary>
        /// Dumps a sheet to Console.
        /// </summary>
        private void Dump()
        {
            for (int i = mData.GetLowerBound(0); i <= mData.GetUpperBound(0); i++)
            {
                for (int j = mData.GetLowerBound(1); j <= mData.GetUpperBound(1); j++)
                {
                    Console.Write(mData[i, j]);
                    Console.Write(" ");
                }
                Console.WriteLine("");
            }
        }

        /// <summary>
        /// Validates the state of the sheet.
        /// </summary>
        internal void Validate()
        { 
            int lower = mData.GetLowerBound(0);
            int upper = mData.GetUpperBound(0);

            for (int i = lower; i <= upper; i++)
            { 
                int freeIdx = mFreeRows.IndexOf(i);
                int rowIdx = mRowsMap.IndexOf(i);
                if (freeIdx >= 0 && rowIdx >= 0)
                    throw new Exception("The row is in both mFreeRows and mRowsMap");
                else if (freeIdx < 0 && rowIdx < 0)
                    throw new Exception("The row is not found in mFreeRows and mRowsMap");
            }
        }

        #endregion

        #region Data fields

        /// <summary>
        /// All data from an Xls are here. First dimension corresponds to a row
        /// and second one is for columns. Note that index of a row in this
        /// array is not a real Xls row index (see mRowsMap) and the array can 
        /// contain free rows after removing (see mFreeRows).
        /// </summary>
        private object[,] mData;

        /// <summary>
        /// Maps an Xls row number to an index in mData.
        /// </summary>
        private IList<int> mRowsMap = new List<int>();

        /// <summary>
        /// Contains indexes of free rows in mData.
        /// </summary>
        private IList<int> mFreeRows = new List<int>();

        /// <summary>
        /// The used columns count.
        /// </summary>
        int mColumns;

        #endregion
    }
}
